FTD Over Time Case Study

Goal: Provide information about First Time Deposits (FTD) over time on client level.

FTD is calculated as the first deposit a client made across all of their logins.

import pandas as pd
import numpy as np

import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots

import plotly.io as pio
pio.renderers.default = "notebook"

daily_ftd = pd.read_csv('./../../data/report/daily_ftd.csv', index_col=0, parse_dates=['ftd_date'])
monthly_ftd = pd.read_csv('./../../data/report/monthly_ftd.csv', index_col=0)
forecast_ftd = pd.read_csv('./../../data/report/forecast_daily_ftd.csv', index_col=0, parse_dates=['ftd_date'])

1. FTD Key Metrics

Metric	Value
Total FTDs	5,294
Total FTD Volume	$3,006,417 USD
Average FTD	$568 USD
Median FTD	$316 USD
FTD Conversion Rate	99.4% (5,294 / 5,325 clients)

FTD amounts are right-skewed (mean >> median), indicating a small number of high-value first deposits pulling the average up.

2. FTD Over Time - Daily

fig = make_subplots(rows=2, cols=1, subplot_titles=('Daily FTD Count', 'Daily FTD Volume (USD)'))

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['count'], 
                         mode='lines', name='Count'), row=1, col=1)
fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['volume'], 
                         mode='lines', name='Volume'), row=2, col=1)

fig.update_layout(height=500, showlegend=False)
fig.update_yaxes(title_text='Count', row=1, col=1)
fig.update_yaxes(title_text='USD', row=2, col=1)
fig.show()

Daily FTD count shows high variability with weekday/weekend patterns. Volume follows a similar shape but with more pronounced spikes from individual high-value deposits.

3. FTD Over Time - Monthly

fig = px.bar(monthly_ftd, x='ftd_month', y='count',
             title='Monthly FTD Count',
             labels={'ftd_month': 'Month', 'count': 'FTD Count'})
fig.update_layout(height=400)
fig.show()

fig = px.bar(monthly_ftd, x='ftd_month', y='volume',
             title='Monthly Volume FTD Amount (USD)',
             labels={'ftd_month': 'Month', 'volume': 'Volume (USD)'})
fig.update_layout(height=400)
fig.show()

display(monthly_ftd[['ftd_month', 'count', 'volume', 'avg_amount']])

	ftd_month	count	volume	avg_amount
0	2025-01	876	487815.769037	556.867316
1	2025-02	666	429361.021236	644.686218
2	2025-03	358	230712.599879	644.448603
3	2025-04	402	299541.733613	745.128691
4	2025-05	357	252317.334185	706.771244
5	2025-06	370	158618.551086	428.698787
6	2025-07	252	124290.961312	493.218100
7	2025-08	756	291496.712857	385.577663
8	2025-09	513	247469.630934	482.396941
9	2025-10	211	129346.309601	613.015685
10	2025-11	106	60465.904713	570.433063

Monthly FTD count is highest in January (876) and August (756). Later months show fewer FTDs as the observation window narrows (data ends Nov 2025). Average FTD amount fluctuates between $386–$745/month with no clear trend.

4. FTD by Registration Type

Type	FTD Count	Avg FTD (USD)	Share
Full	4,504	$574	85.1%
Demo	427	$691	8.1%
Light	363	$344	6.9%

Full registrations account for the vast majority of FTDs. Demo accounts have the highest average FTD, while Light registrations have the lowest.

5. FTD Forecast (Next 30 Days)

fig = make_subplots(rows=2, cols=1, subplot_titles=('FTD Count: Actual + Forecast', 'FTD Volume: Actual + Forecast'))

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['count'], 
                         mode='lines', name='Actual'), row=1, col=1)
fig.add_trace(go.Scatter(x=forecast_ftd['ftd_date'], y=forecast_ftd['count'], 
                         mode='lines', name='Forecast', line=dict(dash='dash', color='red')), row=1, col=1)

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['volume'], 
                         mode='lines', name='Actual', showlegend=False), row=2, col=1)
fig.add_trace(go.Scatter(x=forecast_ftd['ftd_date'], y=forecast_ftd['volume'], 
                         mode='lines', name='Forecast', showlegend=False, line=dict(dash='dash', color='red')), row=2, col=1)

fig.update_layout(height=500)
fig.update_yaxes(title_text='Count', row=1, col=1)
fig.update_yaxes(title_text='USD', row=2, col=1)
fig.show()

display(f'30-day forecast: {forecast_ftd["count"].sum():.0f} FTDs, ${forecast_ftd["volume"].sum():,.0f} volume')

'30-day forecast: 76 FTDs, $342,541 volume'

A rolling MA(5) model was used for the 30-day forecast (best performer among MA windows 3–21, MAE = 3.25, RMSE = 3.86). The forecast converges to ~2.5 FTDs/day (~$11,650/day), producing a conservative estimate of 76 FTDs and $342,541 in total volume for December 2025. The convergence is an inherent limitation of moving average forecasting — it cannot capture trends or seasonality.

6. Additional Findings

• Account structure: 74% of clients have a single trading account; average is 1.47 logins per client.
• Total transaction volume: $4.55M USD across 7,763 transactions (including non-FTD deposits).
• Temporal patterns: Transaction activity is concentrated on weekdays with a notable drop on weekends.
• Data quality: After cleaning, referential integrity holds across all tables. Transaction amounts were converted to USD using ECB historical rates.

7. Limitations

• Data covers Jan–Nov 2025 only; later months have fewer observations.
• The near-100% FTD conversion rate suggests this dataset represents active/depositing clients, not the full client population.
• MA forecasts converge to a flat value and cannot model trends or seasonality. More advanced methods (ARIMA, Prophet) would be needed for longer-horizon predictions.
• All amounts converted to USD using ECB rates — minor discrepancies possible for exotic currencies.